﻿Imports System.Xml
Imports System.Data.SqlClient
''' <summary>
''' 將 SQL 語法 集中於此 Class 處理
''' </summary>
''' <remarks></remarks>
Public Class clsDBUtil

    Private oLog As New clsLog
    Private mCn As SqlClient.SqlConnection = Nothing 'OleDb.OleDbConnection
    Private mCnStr As String = ""
    Private mTrans As SqlClient.SqlTransaction   'OleDb.OleDbTransaction

    Private mDB_IP As String = ""
    Private mDB_Port As String = ""
    Private mDB_Name As String = ""
    Private mDB_Uid As String = ""
    Private mDB_Pwd As String = ""

    Private isTrans As Boolean = False

    Public ReadOnly Property DBLogFolder() As String
        Get
            Return "DBLog"

        End Get

    End Property


    ''' <summary>
    ''' 建立资料庫連線
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub CreateConn()
        Try
            mCn = Nothing
            mCn = New SqlClient.SqlConnection(mCnStr) 'New OleDb.OleDbConnection(mOraConnStr)
            mCn.Open()
        Catch ex As Exception
            Throw New Exception(ex.ToString)
        End Try


    End Sub



    ''' <summary>
    ''' 起始 transaction
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub BeginTrans()
        If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
            CreateConn()
        End If
        mTrans = mCn.BeginTransaction
        isTrans = True
    End Sub

    ''' <summary>
    ''' 完成交易
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Commit()
        Try
            mTrans.Commit()
        Catch ex As Exception

        Finally
            isTrans = False
        End Try

    End Sub

    ''' <summary>
    ''' 回復交易
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub RollbackTrans()
        Try
            mTrans.Rollback()
        Catch ex As Exception

        Finally
            isTrans = False
        End Try

    End Sub

    ''' <summary>
    ''' 執行非查詢指令,執行成功回傳0,失敗 丟出exception
    ''' </summary>
    ''' <param name="sSQL">輸入的SQL語法為帶參數的語法
    ''' Ex: insert into test(a01,a02) values(:a,:b)"
    ''' </param>
    ''' <param name="oParam" ></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(ByVal sSQL As String, ByVal oParam() As SqlParameter) As String
        Dim oCMD As SqlClient.SqlCommand = Nothing
        Try
            'Log SQL Command 
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, sSQL)
            'Log Parameter
            If Not IsNothing(oParam) Then
                For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                    oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "parameter : [ " & oParam(i).ParameterName & " = " & oParam(i).Value & "]")
                Next
            End If


            If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
                CreateConn()
            End If
           
            oCMD = New SqlCommand
            oCMD.CommandText = sSQL
            oCMD.Connection = mCn
            If isTrans Then
                oCMD.Transaction = mTrans
            End If
            oCMD.Parameters.AddRange(oParam)
            oCMD.ExecuteNonQuery()
            Return "0"
        Catch ex As Exception
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, ex.ToString)
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Statement [" & sSQL & "]")
            Throw New Exception(ex.ToString)
        Finally
            'oCMD.Dispose()
            oCMD = Nothing
            Erase oParam
        End Try
    End Function

    ''' <summary>
    ''' 執行非查詢指令,執行成功回傳0,失敗 丟出exception
    ''' </summary>
    ''' <param name="sSQL">完整可執行的SQL</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(ByVal sSQL As String) As String
        Dim oCMD As SqlCommand = Nothing
        Try
            'Log SQL Command 
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, sSQL)
            If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
                CreateConn()
            End If

            oCMD = New SqlCommand
            oCMD.CommandText = sSQL
            oCMD.Connection = mCn
            If isTrans Then
                oCMD.Transaction = mTrans
            End If
            oCMD.ExecuteNonQuery()
            Return "0"
        Catch ex As Exception
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, ex.ToString)
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Statement [" & sSQL & "]")

            Throw New Exception(ex.ToString)
        Finally
            'oCMD.Dispose()
            oCMD = Nothing
        End Try

    End Function


    ''' <summary>
    ''' 執行查詢指令,成功回傳DataTable
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteDT(ByVal sSQL As String, ByVal oParam() As SqlParameter) As DataTable
        Dim oDT As DataTable
        Dim oDS As DataSet
        Dim oDataAdapter As SqlDataAdapter   'OleDb.OleDbDataAdapter  'OracleDataAdapter
        Dim oCMD As SqlCommand = Nothing
        Try
            'Log SQL Command 
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, sSQL)
            'Log Parameter
            If Not IsNothing(oParam) Then
                For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                    oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Parameter : [ " & oParam(i).ParameterName & " = " & oParam(i).Value & "]")
                Next
            End If
            If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
                CreateConn()
            End If
            'oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, sSQL)

            oCMD = New SqlCommand
            oCMD.CommandText = sSQL
            oCMD.Connection = mCn
            If isTrans Then
                oCMD.Transaction = mTrans
            End If
            oCMD.Parameters.AddRange(oParam)
            oDataAdapter = New SqlDataAdapter(oCMD) ' New OleDb.OleDbDataAdapter(sSQL, mOraConn) 'OracleDataAdapter(sSQL, mOraConn)
            oDS = New DataSet
            oDataAdapter.Fill(oDS)
            If oDS.Tables.Count > 0 Then
                oDT = oDS.Tables(0)
            Else
                oDT = Nothing
            End If
            Return oDT
        Catch ex As Exception
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, ex.ToString)
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Statement [" & sSQL & "]")

            Throw New Exception(ex.ToString)
        Finally
            'oCMD.Dispose()
            oCMD = Nothing
            oDataAdapter = Nothing
            oDS = Nothing
            If isTrans = False Then
                mCn.Close()
            End If
        End Try

    End Function

    ''' <summary>
    ''' 執行查詢指令,成功回傳DataTable
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteDT(ByVal sSQL As String) As DataTable
        Dim oDT As DataTable
        Dim oDS As DataSet
        Dim oDataAdapter As SqlDataAdapter  'OleDb.OleDbDataAdapter  'OracleDataAdapter
        Dim oCMD As SqlCommand = Nothing
        Try
            'Log SQL Command 
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, sSQL)
            If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
                CreateConn()
            End If
            oCMD = New SqlCommand
            oCMD.CommandText = sSQL
            oCMD.Connection = mCn
            If isTrans Then
                oCMD.Transaction = mTrans
            End If

            oDataAdapter = New SqlDataAdapter(oCMD) ' New OleDb.OleDbDataAdapter(sSQL, mOraConn) 'OracleDataAdapter(sSQL, mOraConn)
            oDS = New DataSet
            oDataAdapter.Fill(oDS)
            If oDS.Tables.Count > 0 Then
                oDT = oDS.Tables(0)
            Else
                oDT = Nothing
            End If
            Return oDT
        Catch ex As Exception
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, ex.ToString)
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Statement [" & sSQL & "]")

            Throw New Exception(ex.ToString)
        Finally
            'oCMD.Dispose()
            oCMD = Nothing
            oDataAdapter = Nothing
            If isTrans = False Then
                mCn.Close()
            End If
        End Try

    End Function

    ''' <summary>
    ''' 執行Stored Procedure
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteSP(ByVal SPName As String, _
                              ByVal oParam() As SqlParameter) As String
        Dim oCMD As SqlCommand = Nothing
        Try
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Procedure [" & SPName & "]")
            For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Parameter [" & oParam(i).ParameterName & " = " & oParam(i).Value & "]")
            Next

            If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
                CreateConn()
            End If
            oCMD = New SqlCommand
            With oCMD
                .Connection = mCn
                .CommandType = System.Data.CommandType.StoredProcedure
                .CommandText = SPName
                If isTrans Then
                    .Transaction = mTrans
                End If
                For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                    .Parameters.Add(oParam(i))
                Next
                .ExecuteNonQuery()
            End With

            Return "0"
        Catch ex As Exception
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, ex.ToString)
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Procedure [" & SPName & "]")
            For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Parameter [" & oParam(i).ParameterName & "=" & oParam(i).Value & "]")
            Next
            Throw New Exception(ex.ToString)
        Finally
            'oCMD.Dispose()
            oCMD = Nothing
            If isTrans = False Then
                mCn.Close()
            End If
        End Try
    End Function



    ''' <summary>
    ''' 執行Stored Procedure
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteSPGetDT(ByVal SPName As String, _
                              ByVal oParam() As SqlParameter) As DataSet
        Dim oCMD As SqlCommand = Nothing
        Dim ds As New DataSet
        Dim oDataAdpter As SqlClient.SqlDataAdapter
        Try
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Procedure [" & SPName & "]")
            For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Parameter [" & oParam(i).ParameterName & " = " & oParam(i).Value & "]")
            Next

            If mCn Is Nothing OrElse Not (mCn.State = System.Data.ConnectionState.Open) Then
                CreateConn()
            End If
            oCMD = New SqlCommand
            With oCMD
                .Connection = mCn
                .CommandType = System.Data.CommandType.StoredProcedure
                .CommandText = SPName
                If isTrans Then
                    .Transaction = mTrans
                End If
                For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                    .Parameters.Add(oParam(i))
                Next
                oDataAdpter = New SqlClient.SqlDataAdapter(oCMD)
                ds = New DataSet
                oDataAdpter.Fill(ds)
            End With

            Return ds
        Catch ex As Exception
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, ex.ToString)
            oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Error Procedure [" & SPName & "]")
            For i As Integer = oParam.GetLowerBound(0) To oParam.GetUpperBound(0)
                oLog.writeLine("", "", "", DBLogFolder, clsLog.enumMsgType.SQLMsg, "Parameter [" & oParam(i).ParameterName & "=" & oParam(i).Value & "]")
            Next
            Throw New Exception(ex.ToString)
        Finally
            'oCMD.Dispose()
            oCMD = Nothing
            If isTrans = False Then
                mCn.Close()
            End If
        End Try
    End Function

    ''' <summary>
    ''' 起始DB元件需傳入相關資訊
    ''' </summary>
    ''' <param name="DBHost"></param>
    ''' <param name="DBPort"></param>
    ''' <param name="DBName"></param>
    ''' <param name="DBUid"></param>
    ''' <param name="DBPwd"></param>
    ''' <remarks></remarks>
    Public Sub New(ByVal DBHost As String, _
                   ByVal DBPort As String, _
                   ByVal DBName As String, _
                   ByVal DBUid As String, _
                   ByVal DBPwd As String)

        mDB_IP = DBHost
        mDB_Port = DBPort
        mDB_Name = DBName
        mDB_Uid = DBUid
        mDB_Pwd = DBPwd

        mCnStr = "Data Source=" & mDB_IP & "," & mDB_Port & ";Network Library=DBMSSOCN;"
        mCnStr = mCnStr & " Initial Catalog=" & DBName & ";User ID=" & DBUid & ";Password=" & DBPwd & ";"

    End Sub

    Public Sub New(ByVal cnStr As String)
        mCnStr = cnStr
    End Sub

    Public Sub New()
        mDB_IP = Configuration.ConfigurationManager.AppSettings("DB_IP")
        mDB_Port = Configuration.ConfigurationManager.AppSettings("DB_Port")
        mDB_Name = Configuration.ConfigurationManager.AppSettings("DB_Name")
        mDB_Uid = clsCryptoUtil.Decrypt(Configuration.ConfigurationManager.AppSettings("DB_UserID"))
        mDB_Pwd = clsCryptoUtil.Decrypt(Configuration.ConfigurationManager.AppSettings("DB_Pwd"))

        mCnStr = "Data Source=" & mDB_IP & "," & mDB_Port & ";Network Library=DBMSSOCN;"
        mCnStr = mCnStr & " Initial Catalog=" & mDB_Name & ";User ID=" & mDB_Uid & ";Password=" & mDB_Pwd & ";Max Pool Size=1000;"
    End Sub

    Protected Overrides Sub Finalize()
        Try
            If Not IsNothing(mCn) Then
                mCn.Close()
                mCn = Nothing
            End If
            MyBase.Finalize()
        Catch ex As Exception

        End Try

    End Sub
End Class

